IF NOT EXISTS (SELECT * FROM sys.databases WHERE name='DrsagitnDB')
CREATE DATABASE DrsagitnDB

USE DrsagitnDB

GO
/*************************************************************************************************
	Table tgPerson
	Name: tgPerson
	Purpose: Contain the information of users in the system
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgPerson')
CREATE TABLE tgPerson
(
	[PEID]			INT NOT NULL IDENTITY,
	[FirstName]		NVARCHAR(50) NOT NULL,
	[MiddleName]	NVARCHAR(50),
	[LastName]		NVARCHAR(50) NOT NULL,
	[Address]		NVARCHAR(255)
	CONSTRAINT pk_tgPerson_PEID PRIMARY KEY (PEID)	
)

GO
/*************************************************************************************************
	Table tgUser
	Name: tgUser
	Purpose: Contain all user in the system
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgUser')
CREATE TABLE tgUser
(
	[UserID]	INT NOT NULL IDENTITY,
	[UserName]	VARCHAR(50) NOT NULL,
	[Password]	VARCHAR(255) NOT NULL,
	[PEID]		INT
	
	CONSTRAINT pk_tgUser_UserID PRIMARY KEY (UserID)	
	
	CONSTRAINT fk_tgUser_tgPerson_PEID FOREIGN KEY (PEID) REFERENCES tGPerson(PEID)  
)

GO

/*************************************************************************************************
	Table tgHomeCategory
	Name: tgHomeCategory
	Purpose: Contain all categorys in the system. This is main category in our life
	(Website nao cung can co "danh muc chinh" de tao menu...)
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgHomeCategory')
CREATE TABLE tgHomeCategory
(
	[CatID]			INT NOT NULL IDENTITY,
	[CategoryName]	VARCHAR(50) NOT NULL
	
	CONSTRAINT pk_tgCategory_CatID PRIMARY KEY (CatID)	
)

GO
	
	
/*************************************************************************************************
	Table tgDomain
	Name: tgDomain
	Purpose: Contain all domains in the system. This is sub-level 1 of Category
	
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgDomain')
CREATE TABLE tgDomain
(
	[DMID]			INT NOT NULL IDENTITY,
	[DomainName]	VARCHAR(100) NOT NULL,
	[CatID]			INT
	
	CONSTRAINT pk_tgDomain_DMID PRIMARY KEY (DMID)
	
	CONSTRAINT fk_tgDomain_tgHomeCategory FOREIGN KEY(CatID) REFERENCES	tgHomeCategory(CatID)
)

	
/*************************************************************************************************
	Table tgArea
	Name: tgArea
	Purpose: Contain all area in the system. This is sub-level 1 of Domain
	
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgArea')
CREATE TABLE tgArea
(
	[AID]			INT NOT NULL IDENTITY,
	[AreaName]		VARCHAR(100) NOT NULL,
	[DMID]			INT
	
	CONSTRAINT pk_tgArea_AID PRIMARY KEY (AID)
	
	CONSTRAINT fk_tgArea_tgDomain FOREIGN KEY(DMID) REFERENCES tgDomain(DMID)
)

GO
/*************************************************************************************************
	Table tgCompany
	Name: tgCompany
	Purpose: Contain all companys in the system
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgCompany')
CREATE TABLE tgCompany
(
	[CID]			INT NOT NULL IDENTITY,
	[CompanyName]	NVARCHAR(255) NOT NULL,
	[Country]		NVARCHAR(50),
	[Address]		NVARCHAR(255) NOT NULL,
	[SelfIntro]		NVARCHAR(255),		-- self introduction of company
	[CatID]			INT
	
	CONSTRAINT pk_tgCompany_PEID PRIMARY KEY (CID)	
	
	CONSTRAINT fk_tgCompany_tgCategory_CatID FOREIGN KEY (CatID) REFERENCES tgHomeCategory(CatID)  
)

GO

/*************************************************************************************************
	Table tgCompanyUser
	Name: tgCompanyUser
	Purpose: Contain all companys user in the system
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgCompanyUser')
CREATE TABLE tgCompanyUser
(
	[CUID]			INT NOT NULL IDENTITY,
	[ComUserName]	NVARCHAR(255) NOT NULL,
	[CID]			INT -- This user belong to which company
	
	CONSTRAINT pk_tgCompanyUser_CUID PRIMARY KEY (CUID)	
	
	CONSTRAINT fk_tgCompanyUser_tgCompany_CID FOREIGN KEY (CID) REFERENCES tgCompany(CID)  
)

GO
/*************************************************************************************************
	Table tgProduct
	Name: tgProduct
	Purpose: Contain all kind of products in the system
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgProduct')
CREATE TABLE tgProduct
(
	[ProductID]			INT NOT NULL IDENTITY,
	[ProductName]		NVARCHAR(255) NOT NULL,
	[Country]			NVARCHAR(50),
	[Address]			NVARCHAR(255) NOT NULL,
	[AID]				INT
	
	CONSTRAINT pk_tgProduct_ProductID PRIMARY KEY (ProductID)	
	
	CONSTRAINT fk_tgProduct_tgArea_AID FOREIGN KEY (AID) REFERENCES tgArea(AID)  
)	

GO
/*************************************************************************************************
	Table tgPersonQuestion
	Name: tgPersonQuestion
	Purpose: Contain all person question in the system
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgPersonQuestion')
CREATE TABLE tgPersonQuestion
(
	[PQID]			INT NOT NULL IDENTITY,
	[CategoryID]	VARCHAR(50) NOT NULL,
	[PEID]			INT
	
	CONSTRAINT pk_tgPersonQuestion_PQID PRIMARY KEY (PQID)	
)

GO

/*************************************************************************************************
	Table tgComment
	Name: tgComment
	Purpose: Contain all comments for question 
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgComment')
CREATE TABLE tgComment
(
	[CMID]			INT NOT NULL IDENTITY,
	[PQID]			INT,
	[Comment]		VARCHAR(255) NOT NULL,
	[PEID]			INT,	-- If it's a person comment
	[CID]			INT,	-- If it's a company comment
	[ParentComment]	INT
	
	CONSTRAINT pk_tgComment_CMID PRIMARY KEY (CMID)	
	
	CONSTRAINT fk_tgComment_ParentComment FOREIGN KEY (ParentComment) REFERENCES tgComment(CMID)
	
	--CONSTRAINT fk_tgComment_tgPersonQuestion FOREIGN KEY (PQID) REFERENCES tgPersonQuestion(PQID)
)

GO

/*************************************************************************************************
	Table tgProductRanking
	Name: tgProductRanking
	Purpose: Rank for product (calculate from person ranking and company ranking)
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgProductRanking')
CREATE TABLE tgProductRanking
(
	[PRID]			INT NOT NULL IDENTITY,
	[ProductID]		VARCHAR(255) NOT NULL,
	[Rank]			INT,	-- Star	
	[IsPersonRank]	BIT,	-- Person ranking
	[IsComRank]		BIT		-- Company ranking
	
	CONSTRAINT pk_tgProductRanking_PRID PRIMARY KEY (PRID)	
)

GO

/*************************************************************************************************
	Table tgCommentRanking
	Name: tgCommentRanking
	Purpose: Rank for comment
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgCommentRanking')
CREATE TABLE tgCommentRanking
(
	[CMRID]			INT NOT NULL IDENTITY,
	[ProductID]		VARCHAR(255) NOT NULL,
	[Rank]			INT,	-- Star	
	[CMID]			INT
	
	CONSTRAINT pk_tgCommentRanking_PRID PRIMARY KEY (CMRID)
	
	CONSTRAINT Fk_tgCommentRanking_tgComment_CMID FOREIGN KEY(CMID) REFERENCES tgComment(CMID)		
	
)

GO